package pkg461project;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 *
 * @author ahall1
 */
public class DatabaseUpdate {

    private Connection connection;

    public DatabaseUpdate() throws ClassNotFoundException, SQLException {
        // Prepare a statement to insert a record
        String url = "jdbc:oracle:thin:@localhost:1521:xe";
        String user = "ajhallameyer";
        String password = "Aj101989";

        // Load the JDBC driver
        String driverName = "oracle.jdbc.driver.OracleDriver";
        Class.forName(driverName);

        connection = DriverManager.getConnection(url, user, password);
    }

    public void updateBranch(Branch branch) {
        try {
            String query = "UPDATE BRANCH "
                    + "SET "
                    + "manager_num = ? AND "
                    + "telephone_number = ? AND "
                    + "fax_number = ? AND "
                    + "street = ? AND "
                    + "city = ? AND "
                    + "state = ? AND "
                    + "zipcode = ? "
                    + "WHERE "
                    + "branch_num = ?";

            PreparedStatement ps = connection.prepareStatement(query);
            ps.setInt(1, branch.getManager_num());
            ps.setString(2, branch.getTelephone_number());
            ps.setString(3, branch.getFax_number());
            ps.setString(4, branch.getState());
            ps.setString(5, branch.getCity());
            ps.setString(6, branch.getState());
            ps.setInt(7, branch.getZipcode());
            ps.setInt(8, branch.getBranch_num());

            ps.executeUpdate();

        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
    }

    public void updateEmployee(Employee employee, int num) {
        try {
            String query = "UPDATE EMPLOYEE "
                    + "SET "
                    + "name = ? AND "
                    + "sex = ? AND "
                    + "street = ? AND "
                    + "city = ? AND "
                    + "state = ? AND "
                    + "zipcode = ? "
                    + "WHERE "
                    + "employee_num = ?";

            PreparedStatement ps = connection.prepareStatement(query);

            ps.setString(1, employee.getName());
            ps.setString(2, employee.getSex());
            ps.setString(3, employee.getStreet());
            ps.setString(4, employee.getCity());
            ps.setString(5, employee.getState());
            ps.setInt(6, employee.getZipcode());
            ps.setInt(7, employee.getEmployee_num());

            ps.executeUpdate();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
    }

    public void updateLease(Lease lease) {
        try {
            String query = "UPDATE Lease "
                    + "SET "
                    + "start_date = ? AND "
                    + "finish_date = ? AND "
                    + "payment_method = ? AND "
                    + "duration = ? AND "
                    + "deposit_amount = ? AND "
                    + "property_num = ? "
                    + "WHERE "
                    + "employee_num = ?";

            PreparedStatement ps = connection.prepareStatement(query);

            ps.setDate(1, lease.getStart_date());
            ps.setDate(2, lease.getFinish_date());
            ps.setString(3, lease.getPayment_method());
            ps.setInt(4, lease.getDuration());
            ps.setInt(5, lease.getDeposit_amount());
            ps.setInt(6, lease.getProperty_num());
            ps.setInt(7, lease.getLease_num());

            ps.executeUpdate();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
    }

    public void updateNewspaperAd(Newspaper_ad newsAd) {
        try {
            String query = "UPDATE NEWSPAPER_AD "
                    + "SET "
                    + "property_num = ? AND "
                    + "newspaper_name = ? AND "
                    + "cost_of_ad = ? AND "
                    + "date_of_ad = ? AND "
                    + "street = ? AND "
                    + "city = ? AND "
                    + "state = ? AND "
                    + "zipcode = ? "
                    + "WHERE "
                    + "newspaper_ad_num = ?";

            PreparedStatement ps = connection.prepareStatement(query);

            ps.setInt(1, newsAd.getProperty_num());
            ps.setString(2, newsAd.getNewspaper_name());
            ps.setInt(3, newsAd.getCost_of_ad());
            ps.setDate(4, newsAd.getDate_of_ad());
            ps.setString(5, newsAd.getStreet());
            ps.setString(6, newsAd.getCity());
            ps.setString(7, newsAd.getState());
            ps.setInt(8, newsAd.getZipcode());
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
    }

    public void close() throws SQLException {
        connection.close();
    }
}
